/*
Run this script on:

        mssql-ct.db.jwan.it,1433.Caronte2013    -  This database will be modified

to synchronize it with:

        kalgan01.cs.jwan.it,1443.Jambo2014_sviluppo

You are recommended to back up your database before running this script

Script created by SQL Compare version 9.0.0 from Red Gate Software Ltd at 10/04/2014 15:03:16

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Altering [dbo].[v_EsportaAnek]'
GO


/* C.M. 2014-03-20 - aggiunta di macroregione e bts e modifiche alle join*/
ALTER VIEW [dbo].[v_EsportaAnek]
--WITH ENCRYPTION 
WITH ENCRYPTION AS

SELECT     m.SERIAL, m.CODAGE, a.NOME, a.AGEMADRE, a.DARENDICONTARE AS affidate, a.TIPO_BIGLIETTERIA, an.DESCRIZIONE AS network, 
                      ans.DATA_ENTRATA AS entrata_network, ans.DATA_USCITA AS uscita_network, a.RID, a.COD_REG, r.DESCRIZION AS regione, a.STATO, a.CITTA, 
                      m.DATAMOV, m.CODPREN, m.N_SEQ,(m.importo+m.tasse)as LORDO, m.IMPORTO, m.NETTO,m.TASSE, a.PROF_COMM, m.COMMISSIONE, m.TIPOMOV, c.DAREAVERE, c.DESCRIZIO, 
                      m.FORMAPAG, m.DATAPAG, m.COD_LOCALE, b.CODPROV, b.NOME AS intestatario,
s.DATAPART AS partenza_andata_ultimapratica,
i.PARTENZA AS porto_andata_ultimapratica,
s.FORMULA AS formula_andata_ultimapratica,
s.TIPOTK AS tipotk, b.RECAPITO, bi.DATA_EMISS AS data_emissione_biglietto,
l.MACROREGIONE AS macroregione,
b.codonline as codice_BTS

FROM dbo.movimenti AS m 
INNER JOIN dbo.agenzi AS a ON m.CODAGE = a.CODAGE 
LEFT OUTER JOIN dbo.agenzie_network_storico AS ans ON ans.CODAGE = a.CODAGE 
LEFT OUTER JOIN  dbo.agenzie_network AS an ON an.COD_NETWORK = ans.COD_NETWORK 
INNER JOIN dbo.causalimovim AS c ON c.TIPOMOV = m.TIPOMOV 
INNER JOIN dbo.region AS r ON r.COD_REG = a.COD_REG 
INNER JOIN dbo.biglie AS b ON m.CODPREN = b.CODPREN  
INNER JOIN dbo.seqbig AS s ON m.CODPREN = s.CODPREN AND s.N_SEQ = 1 
INNER JOIN dbo.itiner AS i ON i.CODITINERA = s.CODITINERA 
INNER JOIN corse co ON s.codcorsa = co.codcorsa
INNER JOIN configlinee cl ON cl.cod_cfg_linea = co.cod_cfg_linea
INNER JOIN linee l ON l.codlin = cl.codlin
LEFT OUTER JOIN dbo.biglietti AS bi ON bi.CODPREN = m.CODPREN AND bi.N_SEQ = m.N_SEQ AND bi.N_MOD = m.N_MOD AND bi.RIEMESSO = 0
WHERE b.annota <> 'ANNULLATA DA BATCH'

GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Altering [dbo].[jg_loggestIns]'
GO

-- VM 22-01-2014 Implementata registrazione di alcuni dettagli su specifiche modifiche, 
-- VM 20-06-2012 gestione campi CANCELLABILE e CODOCORSA e ottimizzazione filtro cancellazione
-- VM 19-06-2012 aggiunta condizione di filtro su quali log eliminare. Momentaneamente tutte le operazioni su corse e disp. vengono mantennute.
-- VM 23-04-2012 log_date inserito come timestamp del db e non passato dal Gest
-- VM 05-04-2012 Aggiunta istruzione per ripulire i log fino all'ultimo mese.

ALTER PROCEDURE [dbo].[jg_loggestIns] 
@user varchar(20),
@workstation varchar(30),
@role varchar(30),
@log_level varchar(20),
@message varchar(4000), 
@exception varchar(2000),
@codcorsa char(16) = NULL,
@CODAGE VARCHAR(6)=NULL,
@PROF_COMMISSIONE CHAR(2)=NULL,
@FIDO DECIMAL(18,2)=NULL,
@DARENDICONTARE CHAR(1)=NULL

-- WITH ENCRYPTION
WITH ENCRYPTION AS
declare @operation varchar(10)
set @operation = substring(@message, 0,10)
set @message = substring(@message,11,3990)


INSERT INTO [LogGest] 
([Date],[Level],[User],[Role],[Workstation],[Operation],[Message],[Exception], CODCORSA, 
	CODAGE, PROF_COMMISSIONE, FIDO, DARENDICONTARE) 
VALUES (GETDATE(), @log_level, @user, @role, @workstation, @operation, @message, @exception, @codcorsa, 
	@CODAGE, @PROF_COMMISSIONE, @FIDO, @DARENDICONTARE);
/*
--cancello i log degli ultimi 3 mesi
DECLARE @dateDelete datetime
--arrotondo la data
SET @dateDelete = DATEADD(Day, DATEDIFF(Day, 0, getdate()),0)
--vado indietro di 3 mesi
SET @dateDelete = DATEADD(Month, -3, @dateDelete)
DELETE LogGest WHERE [Date] < @dateDelete and CANCELLABILE = 1 OR CANCELLABILE IS NULL-- operation NOT IN ('CorseDisp', 'chgTariff', '+CatCorsa', 'chgCorsa', 'chgNave', 'chgOrario', 'CorseLim')
*/
IF @@ERROR <> 0
	RAISERROR 55555 'jg_logger'


GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
